use master
go
if exists(select * from sys.databases where name='shopping')
drop database shopping
go
create database shopping
go
use shopping
go
create table tbl_admin
(
	adm_id int identity(1,1) primary key,
	adm_username varchar(25),
	adm_password varchar(25),
	[role] int default(1)
)
go
insert into tbl_admin(adm_username,adm_password) values('admin@domain.com','admin')
go
create table tbl_category
(
	cat_id int identity(1,1),
	cat_name nvarchar(50),
	cat_description nvarchar(200),
	constraint pk_cat_id primary key (cat_id)
)
go
create table tbl_product
(
	pd_id int identity(1,1),
	cat_id int,
	pd_name nvarchar(50),
	pd_description nvarchar(200),
	pd_quantity int,
	pd_price money,
	pd_image nvarchar(200),
	pd_date datetime default(getdate()),
	pd_visit int default(0),
	pd_ordernum int default(0)
	constraint pk_pd_id primary key (pd_id),
	constraint fk_pd_cat foreign key (cat_id) references tbl_category(cat_id)
)

go
create table tbl_customer
(
	cus_id int identity(1,1),
	cus_firstname varchar(50) not null,
	cus_lastname varchar(50) not null,
	cus_dateOfbirth datetime,
	cus_gender bit default(2),
	cus_address nvarchar(250),	
	cus_email varchar(50),
	cus_password varchar(20) not null,
	cus_phone varchar(15),
	[role] int default(0),
	constraint pk_customerid primary key (cus_id)	
)
go
create table tbl_order
(
	od_id int identity(1,1),
	od_code varchar(15),
	cus_id int,
	od_date datetime,
	od_status nvarchar(50),
	od_shipping_firstname nvarchar(50),
	od_shipping_lastname nvarchar(50),
	od_shipping_email varchar(30),
	od_shipping_address nvarchar(250),
	od_shipping_phone nvarchar(15),
	od_shipping_state nvarchar(20),
	od_shipping_city nvarchar(20),
	od_shipping_zipcode varchar(10),
	od_payment_name nvarchar(50),
	od_payment_cardtype nvarchar(20),
	od_payment_cardnum nvarchar(15),
	od_payment_expridate datetime,
	od_payment_verinum varchar(20),
	od_quantity int,
	od_totalprice money
	constraint pk_od_id primary key (od_id),
	constraint fk_od_cus foreign key (cus_id) references tbl_customer(cus_id)
)

go
create table tbl_order_item
(
	od_id int,
	pd_id int,
	odi_quantity int,
	odi_price money	
	constraint fk_orderitem_order foreign key (od_id) references tbl_order(od_id),
	constraint fk_order_product foreign key (pd_id) references tbl_product(pd_id)
)
go
create table tbl_hitcounter
(
	hitcount int default(0),
	lastupdate datetime
)
go
insert into tbl_hitcounter(hitcount) values(0)
go
create table tbl_comment
(
	cm_id int identity(1,1),
	pd_id int,
	cus_id int,
	cm_title nvarchar(100),
	cm_content nvarchar(300),
	cm_date datetime default(getdate()),
	cm_status bit,
	constraint pk_comment primary key (cm_id),
	constraint fk_comment_product foreign key (pd_id) references tbl_product(pd_id),
	constraint fk_comment_customer foreign key (cus_id) references tbl_customer(cus_id)
)
-- TABLE RATING
go
create table tbl_rating
(
	pd_id int,
	cus_id int,
	rating int default(0),
	constraint fk_rating_product foreign key (pd_id) references tbl_product(pd_id),
	constraint fk_rating_customer foreign key (cus_id) references tbl_customer(cus_id)
)
go
---------------------------------------------------------------------------------
create proc proc_insertProduct
	@cat_id int,
	@pd_name nvarchar(50),
	@pd_description nvarchar(200),
	@pd_quantity int,
	@pd_price money,
	@pd_image nvarchar(200)
as
insert into tbl_product(cat_id,pd_name,pd_description,pd_quantity,pd_price,pd_image)
values (@cat_id,@pd_name,@pd_description,@pd_quantity,@pd_price,@pd_image)
go
create proc proc_UpdateProduct
	@cat_id int,
	@pd_name nvarchar(50),
	@pd_description nvarchar(200),
	@pd_quantity int,
	@pd_price money,
	@pd_image nvarchar(200),
	@pd_id int
as
update tbl_product
set cat_id=@cat_id,pd_name=@pd_name,pd_description=@pd_description,pd_quantity=@pd_quantity,pd_price=@pd_price,pd_image=@pd_image
where pd_id=@pd_id
--DELETE PRODUCT
go
create proc proc_deleteProduct
	@pd_id int
as
delete tbl_product
where pd_id=@pd_id
go
-- INSERT ORDER
go
create procedure proc_insertOrder
	@od_code varchar(15),
	@cus_id int,
	@od_date datetime,
	@od_status nvarchar(50),
	@od_shipping_firstname nvarchar(50),
	@od_shipping_lastname nvarchar(50),
	@od_shipping_email varchar(30),
	@od_shipping_address nvarchar(250),
	@od_shipping_phone nvarchar(15),
	@od_shipping_state nvarchar(20),
	@od_shipping_city nvarchar(20),
	@od_shipping_zipcode varchar(10),
	@od_payment_name nvarchar(50),
	@od_payment_cardtype nvarchar(20),
	@od_payment_cardnum nvarchar(15),
	@od_payment_expridate datetime,
	@od_payment_verinum varchar(20),
	@od_quantity int,
	@od_totalprice money
as
insert into tbl_order(od_code,cus_id,od_date,od_status,od_shipping_firstname,od_shipping_lastname,od_shipping_email,od_shipping_address,od_shipping_phone,od_shipping_state,od_shipping_city,od_shipping_zipcode,od_payment_name,od_payment_cardtype,od_payment_cardnum,od_payment_expridate,od_payment_verinum,od_quantity,od_totalprice)
values (@od_code,@cus_id,@od_date,@od_status,@od_shipping_firstname,@od_shipping_lastname,@od_shipping_email,@od_shipping_address,@od_shipping_phone,@od_shipping_state,@od_shipping_city,@od_shipping_zipcode,@od_payment_name,@od_payment_cardtype,@od_payment_cardnum,@od_payment_expridate,@od_payment_verinum,@od_quantity,@od_totalprice)
go
create procedure proc_UpdateOrder
	@od_code varchar(15),
	@od_date datetime,
	@od_status nvarchar(50),
	@od_shipping_firstname nvarchar(50),
	@od_shipping_lastname nvarchar(50),
	@od_shipping_email varchar(30),
	@od_shipping_address nvarchar(250),
	@od_shipping_phone nvarchar(15),
	@od_shipping_state nvarchar(20),
	@od_shipping_city nvarchar(20),
	@od_shipping_zipcode varchar(10),
	@od_payment_name nvarchar(50),
	@od_payment_cardtype nvarchar(20),
	@od_payment_cardnum nvarchar(15),
	@od_payment_expridate datetime,
	@od_payment_verinum varchar(20),
	@od_quantity int,
	@od_totalprice money,
	@od_id int
as
update tbl_order
set od_code=@od_code,od_date=@od_date,od_status=@od_status,od_shipping_firstname=@od_shipping_firstname,od_shipping_lastname=@od_shipping_lastname,od_shipping_email=@od_shipping_email,od_shipping_address=@od_shipping_address,od_shipping_phone=@od_shipping_phone,od_shipping_state=@od_shipping_state,od_shipping_city=@od_shipping_city,od_shipping_zipcode=@od_shipping_zipcode,od_payment_name=@od_payment_name,od_payment_cardtype=@od_payment_cardtype,od_payment_cardnum=@od_payment_cardnum,od_payment_expridate=@od_payment_expridate,od_payment_verinum=@od_payment_verinum,od_quantity=@od_quantity,od_totalprice=@od_totalprice
where
od_id=@od_id
select * from tbl_order
go
create procedure proc_insertOrderItem
	@od_id int,
	@pd_id int,
	@odi_quantity int,
	@odi_price money
as
insert into tbl_order_item(od_id,pd_id,odi_quantity,odi_price)
values (@od_id,@pd_id,@odi_quantity,@odi_price)
go
create proc getOrderByCusIdAndCode
	@cus_id int,
	@od_code varchar(15)
as
select od_id from tbl_order
where cus_id=@cus_id and od_code=@od_code
go
create  proc getOrderByCode
	@od_code varchar(15)
as
select * from tbl_order
where od_code=@od_code
go
---------------------------------------------------------------------------------
create procedure proc_insertCustomer
	@cus_firstname varchar(50),
	@cus_lastname varchar(50),
	@cus_dateOfbirth datetime,
	@cus_gender bit,
	@cus_address nvarchar(250),
	@cus_email varchar(50),
	@cus_password varchar(20),
	@cus_phone varchar(15)
as
insert into tbl_customer(cus_firstname,cus_lastname,cus_dateOfbirth,cus_gender,cus_address,cus_email,cus_password,cus_phone)
values (@cus_firstname,@cus_lastname,@cus_dateOfbirth,@cus_gender,@cus_address,@cus_email,@cus_password,@cus_phone)
go
create proc proc_UpdateCustomer
	@cus_firstname varchar(50),
	@cus_lastname varchar(50),
	@cus_dateOfbirth datetime,
	@cus_gender bit,
	@cus_address nvarchar(250),
	@cus_email varchar(50),
	@cus_password varchar(20),
	@cus_phone varchar(15),
	@cus_id int
as
update tbl_customer
set cus_firstname=@cus_firstname,cus_lastname=@cus_lastname,cus_dateOfbirth=@cus_dateOfbirth,cus_gender=@cus_gender,cus_address=@cus_address,cus_email=@cus_email,cus_password=@cus_password,cus_phone=@cus_phone
where cus_id=@cus_id
go
--------------------------------------------------------------------------------
go
create proc proc_getCustomerByEmail
	@cus_email varchar(50),
	@cus_password varchar(20)
as 
select * from tbl_customer
where cus_email=@cus_email and cus_password=@cus_password
go
create proc proc_getAdminByUserAndPass
	@adm_username varchar(25),
	@adm_password varchar(25)
as select * from tbl_admin
where adm_username=@adm_username and adm_password=@adm_password
go
---------------------------------------------------------------------------------
create proc proc_GetCategory
as
select * from tbl_category
go
-- UPDATE CATEGORY
create proc proc_UpdateCategory
	@cat_id int,
	@cat_name nvarchar(50),
	@cat_description nvarchar(200)
as
update tbl_category set cat_name=@cat_name,cat_description=@cat_description
where cat_id=@cat_id
go
-- GETCATEGORY BY NAME
create proc proc_GetcategoryByNAME
	@cat_name nvarchar(50)
as 
select cat_id from tbl_category
where cat_name=@cat_name

go
--add category
create proc proc_AddCategory
	@cat_name nvarchar(50),
	@cat_description nvarchar(200)
as
insert into tbl_category(cat_name,cat_description)
values (@cat_name,@cat_description)

go
--delete category
create proc  proc_DeleteCategory
	@cat_id int
as
delete tbl_category
where cat_id=@cat_id
---------------------------------------------------------------------------------
go
--get product
create proc proc_GetProduct
as
select * from tbl_product
---------------------------------------------------------------------------------
go
--get product by id
create proc proc_GetProductByID
	@pd_id int
as
select * from tbl_product
where pd_id = @pd_id
go
create proc proc_GetProductByPrice
	@pd_price money
as
select * from tbl_product
where pd_price=@pd_price
---------------------------------------------------------------------------------
go
create proc proc_searchProductByName
	@pd_name nvarchar(50)
as
select * from tbl_product
where pd_name like '%'+@pd_name+'%'
go
create proc proc_getProductByCategoryID
	@cat_id int
as select * from tbl_product
where cat_id=@cat_id
---------------------------------------------------------------------------------
go
create proc proc_updateHitcount
	@hitcount int,
	@lastupdate datetime
as
update tbl_hitcounter set hitcount = @hitcount, lastupdate = @lastupdate
go
create proc proc_getHitCounter
as
select hitcount from tbl_hitcounter
--PROC TABLE COMMENT
go
create proc proc_InsertComment
	@pd_id int,
	@cus_id int,
	@cm_title nvarchar(100),
	@cm_content nvarchar(300),
	@cm_status bit
as
insert into tbl_comment(pd_id,cus_id,cm_title,cm_content,cm_status)
values (@pd_id,@cus_id,@cm_title,@cm_content,@cm_status)
select * from tbl_comment
--GET COMMENT
go
create proc proc_GetCommentByPDID
	@pd_id int
as
select tbl_comment.cm_title,tbl_comment.cm_content,tbl_comment.cm_date,tbl_customer.cus_firstname,tbl_customer.cus_lastname,tbl_customer.cus_email 
from tbl_comment
inner join tbl_customer
on tbl_comment.cus_id = tbl_customer.cus_id
where pd_id = @pd_id
--INSERT RATING
go
create proc proc_InsertRating
	@pd_id int,
	@cus_id int,
	@rating int
as
insert into tbl_rating(pd_id,cus_id,rating)
values(@pd_id,@cus_id,@rating)
--GET RATING
go
create  proc proc_GetRating
	@pd_id int,
	@cus_id int
as
select * from tbl_rating
where pd_id=@pd_id and cus_id=@cus_id
-- TOTAL CUSTOMER COMMENT
go
create proc proc_GetTotalRatingByPDID
	@pd_id int
as
select * from tbl_rating
where pd_id=@pd_id
-- SUM POINT BY PD ID
go
create proc proc_GetSumPointByPDID
	@pd_id int
as
select sum(rating) as sumPoint
from tbl_rating
where pd_id=@pd_id
select * from tbl_comment
--
-- SELECT ORDER ITEM
go
create proc GetOrderItem
	@od_id int
as
select * from tbl_order_item
where od_id=@od_id
-- UPDATE VISIT PRODUCT
go
create proc proc_UpdateVisitProduct
	@pd_id int,
	@pd_visit int
as
update tbl_product
set pd_visit=@pd_visit
where pd_id=@pd_id
-- GET CURRENT VISIT
go
create proc proc_GetVisitProduct
	@pd_id int
as
select pd_visit from tbl_product
where pd_id=@pd_id
--UPDATE ORDER NUMBER
go
create proc proc_UpdateOrderNum
	@pd_id int,
	@pd_ordernum int
as
update tbl_product
set pd_ordernum=@pd_ordernum
where pd_id=@pd_id
--GET CURRENT ORDER NUM
go
create proc proc_GetCurrentOrderNum
	@pd_id int
as
select pd_ordernum from tbl_product
where pd_id=@pd_id

--